package pkg461project;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 *
 * @author ahall1
 */
public class DatabaseView {
    
    private Connection connection;
    
    public DatabaseView() throws ClassNotFoundException, SQLException {
        // Prepare a statement to insert a record
        String url = "jdbc:oracle:thin:@localhost:1521:xe";
        String user = "ajhallameyer";
        String password = "Aj101989";

        // Load the JDBC driver
        String driverName = "oracle.jdbc.driver.OracleDriver";
        Class.forName(driverName);
        
        connection = DriverManager.getConnection(url, user, password);
    }
    
    public void searchCityBranch(String city) throws SQLException {
        try {
            String query = "SELECT * "
                    + "FROM BRANCH "
                    + "WHERE city = ?";
            
            PreparedStatement ps = connection.prepareStatement(query);
            
            ps.setString(1, city);
            
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numColumns = rsmd.getColumnCount();
            
            while (rs.next()) {
                System.out.println();
                for (int i = 1; i <= numColumns; i++) {
                    System.out.println(rs.getString(i));
                }
            }
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
    public void searchStateBranches(String state) throws SQLException {
        try {
            String query = "SELECT * "
                    + "FROM BRANCH "
                    + "WHERE state = ?";
            
            PreparedStatement ps = connection.prepareStatement(query);
            
            ps.setString(1, state);
            
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int numColumns = rsmd.getColumnCount();
            int count = 0;
            
            while (rs.next()) {
                count++;
            }
            
            System.out.println("\nThere are " + count + " branches in " + state + "\n");
            
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
    
    public void close() throws SQLException {
        connection.close();
    }
}
